package DBLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ExceptionLayer.CustomException;
import ModelLayer.*;

public class DBPerson implements IFDBPerson {
	
	private  Connection con;

	public DBPerson()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	public ArrayList<Person> getCustomersByPhone(String phone) 
	{
		return miscWhere("Type = 'customer' AND PhoneNr = " + phone);
	}
	
	public Person getPersonById(int id)
	{
		return singleWhere("ID = " + id);			
	}
	
	private Person singleWhere(String wClause)
	{
		ResultSet results;
		Person pers = null;
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				pers = buildPerson(results);
			}
			
			stmt.close();
			
		}
		
		catch(Exception e)
		{
			System.out.println("Query Exception - Select: "+e);
			e.printStackTrace();
		}
		
		return pers;
	}
	
	private ArrayList<Person> miscWhere(String wClause)
	{
		ResultSet results;
	    ArrayList<Person> list = new ArrayList<Person>();	
		
	    String query = buildQuery(wClause);
  
	    try
	    { 
			Statement stmt = con.createStatement();
		 	stmt.setQueryTimeout(5);
		 	results = stmt.executeQuery(query);
	 	
	
		 	while(results.next()) {
		 		Person person = buildPerson(results);
 
		 		list.add(person);	
			}
                 
		 	stmt.close(); 
	 	  			
		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
	    
		return list;
	}
	
	private Person buildPerson(ResultSet result)
	{
		Person person = new Person();
		
		try {
			person.setId(result.getInt("ID"));
			person.setType(result.getString("Type"));
			person.setFirstName(result.getString("FirstName"));
			person.setLastName(result.getString("LastName"));
			person.setPhone(result.getString("PhoneNr"));
			person.setEmail(result.getString("Email"));
			person.setDateCreated(result.getDate("DateCreated"));
		}
		catch(Exception err)
		{	
			
		}
		
		return person;
	}
	
	private String buildQuery(String wClause)
	{
		String query = "Select ID, Type, FirstName, LastName, PhoneNr, Email, DateCreated FROM Person";
		
		if(wClause.length() > 0)
			query = query + " WHERE " + wClause;
		
		return query;
	}
	
	public void insertCustomer(Person customer) throws CustomException
    {  
		//call to get the next ssn number
        int nextId = GetMax.getMaxId("Select max(ID) from Person");
        nextId = nextId + 1;
        System.out.println("next id = " +  nextId);
        
        customer.setId(nextId);
        
        try
        {
		    String query ="INSERT INTO Person(ID, Type, FirstName, LastName, PhoneNr, Email) VALUES (?, ?, ?, ?, ?, ?)";

		    PreparedStatement pstmt = con.prepareStatement(query);
		 
		    pstmt.setInt(1, nextId);
		    pstmt.setString(2, customer.getType());
		    pstmt.setString(3, customer.getFirstName());
		    pstmt.setString(4, customer.getLastName());
		    pstmt.setString(5, customer.getPhone());
		    pstmt.setString(6, customer.getEmail());
		    pstmt.executeUpdate();  
        }
        catch (SQLException sqlEx)
        {
        	System.out.println("Insert Customer failed!!");
        	throw new CustomException("Der skete en database fejl!");
        }
	   
    }
	
	
}